﻿using Flare.DB.CeEntity;
using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.Linq;
using System.Reflection;


namespace Flare.DB
{
    class general_departments
    {
        public List<general_department> Index(int? id_department = null, string department = null, string department_address = null,
            string department_contact = null, string department_contact_phone = null, bool? isfilial = null)
        {
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
//                using (SqlCeCommand command = new SqlCeCommand(
//                        @"SELECT [id_department],[department],[department_address],[department_contact],[department_contact_phone],[isfilial] FROM [general_department] WHERE 
//                        (id_department = @id_department OR @id_department IS NULL) AND 
//                        (department = @department OR @department IS NULL) AND 
//                        (department_address = @department_address OR @department_address IS NULL) AND 
//                        (department_contact = @department_contact OR @department_contact IS NULL) AND 
//                        (department_contact_phone = @department_contact_phone OR @department_contact_phone IS NULL) AND
//                        (isfilial = @isfilial OR @isfilial IS NULL) 
//                        ORDER BY [department]", connection))
//                {
//                    command.Parameters.AddWithValue("@id_department", (object)id_department ?? DBNull.Value);
//                    command.Parameters.AddWithValue("@department", (object)department ?? DBNull.Value);
//                    command.Parameters.AddWithValue("@department_address", (object)department_address ?? DBNull.Value);
//                    command.Parameters.AddWithValue("@department_contact", (object)department_contact ?? DBNull.Value);
//                    command.Parameters.AddWithValue("@department_contact_phone", (object)department_contact_phone ?? DBNull.Value);
//                    command.Parameters.AddWithValue("@isfilial", (object)isfilial ?? DBNull.Value);

                using (SqlCeCommand command = new SqlCeCommand(
                        @"SELECT [id_department],[department],[department_address],[department_contact],[department_contact_phone] FROM [general_department]", connection))
                {
                  
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    var departments = new List<general_department>();
                    try
                    {
                        using (SqlCeDataReader reader = command.ExecuteReader())
                        {
                   
                            while (reader.Read())
                            {
                                var current_department = new general_department();
                                current_department.id_department = Convert.ToInt32(reader["id_department"]);
                                current_department.department = reader["department"].ToString();
                                current_department.department_address = reader["department_address"].ToString();
                                current_department.department_contact = reader["department_contact"].ToString();
                                current_department.department_contact_phone = reader["department_contact_phone"].ToString();
                                departments.Add(current_department);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText);
                        glob.kernel.Panic(glob.PanicType.sql, ex);
                    }
                    return departments;
                }
            }
        }

        public void Create(general_department data)
        {
            var validate = data.Validate();
            if (validate.Count() != 0)
            {
                throw new Exception(string.Join<string>("\n", validate));
            }
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(
                        string.Format(@"INSERT INTO [general_department] ([department],[department_address],[department_contact],[department_contact_phone]) VALUES ('{0}','{1}','{2}','{3}')",
                        data.department, data.department_address, data.department_contact, data.department_contact_phone), connection))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время добавления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public void Edit(general_department data)
        {
            var validate = data.Validate();
            if (validate.Count() != 0)
            {
                throw new Exception(string.Join<string>("\n", validate));
            }
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(string.Format(@"UPDATE [general_department] SET [department]='{1}',[department_address]='{2}',[department_contact]='{3}',
[department_contact_phone]='{4}' WHERE [id_department] = {0}", data.id_department, data.department, data.department_address, data.department_contact, data.department_contact_phone), connection))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время обновления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public void Delete(int id_department)
        {
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(string.Format("DELETE FROM [general_department] WHERE [id_department] = {0}", id_department), connection))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText + "\n" + ex.Message);
                        throw new Exception("Во время удаления данных произошла ошибка. Данные не сохранены");
                    }
                }
            }
        }

        public general_department Details(int id_department)
        {
            return new general_department();
        }

        public int Count()
        {
            using (SqlCeConnection connection = new SqlCeConnection(glob.DBConnection))
            {
                using (SqlCeCommand command = new SqlCeCommand(@"SELECT COUNT(*) FROM [general_department]", connection))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (Exception ex)
                    {
                        glob.kernel.Panic(glob.PanicType.db_connect, ex);
                    }

                    int count = 0;
                    try
                    {
                        using (SqlCeDataReader reader = command.ExecuteReader())
                        {
                            reader.Read();
                            count = Convert.ToInt16(reader[0]);
                        }
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(command.CommandText);
                        glob.kernel.Panic(glob.PanicType.sql, ex);
                    }
                    return count;
                }
            }
        }
    }
}
